USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[TaikaiList]    Script Date: 04/24/2014 11:44:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaikaiList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TaikaiList]
GO

CREATE PROCEDURE [dbo].[TaikaiList]
AS

	SELECT TAIKAINO, 
		   CONVERT (nvarchar(10), CONVERT (datetime, KAISI), 111) + '〜' +
           CONVERT (nvarchar(10), CONVERT (datetime, SYURYO), 111) AS KIKAN, 
	 TAIKAIMEI, 
	CASE TAIKAIFLG 
	     WHEN '0' THEN '未開催'
	     WHEN '1' THEN '開催中'
	     WHEN '2' THEN '大会終了' 
	END AS Expr1
    ,TAIKAIFLG
	   FROM TBL_TAIKAI
	  ORDER BY 
		CASE WHEN TAIKAIFLG = 1 THEN -1 ELSE TAIKAIFLG END 
		,KAISI DESC
		,TAIKAINO DESC


GO

